package com.busdepot.webapp.domain;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.simple.SimpleJdbcTemplate;
import org.springframework.stereotype.Component;

import com.busdepot.webapp.util.DbUtils;

@Component("catalogRepository")
public class CatalogRepositoryImpl implements CatalogRepository {
	private SimpleJdbcTemplate simpleJdbcTemplate = DbUtils
			.getSimpleJdbcTemplate();

	public List<Category> getCategoryByModelId(int id) {
		String sql = "select distinct c.id, c.description from categories c, link l where c.id=l.category_id and l.model_id=?";
		return this.simpleJdbcTemplate.query(sql, new CategoryMapper(), id);
	}

	public List<Group> getGroup(int categoryId, int modelId) {
		String sql = "select distinct g.id, g.description from groups g, link l, models m where g.id=l.group_id and l.category_id=? and l.model_id=?";
		return this.simpleJdbcTemplate.query(sql, new GroupMapper(), categoryId, modelId);
	}

	public int getModelId(String model, int startYear, int endYear) {
		String sql = "select m.id from models m where m.description=? and m.from_year=? and m.to_year=?";
		int id = simpleJdbcTemplate.queryForInt(sql, model, startYear, endYear);
		return id;
	}

	public List<Model> getModelsByName(String modelName) {
		String sql = "select m.id, m.description, m.from_year, m.to_year from models m where m.description=?";
		return simpleJdbcTemplate.query(sql, new ModelMapper(), modelName);
	}

	public List<BusDepotPart> getPartsByCategoryId(int id) {
		String sql = "select distinct p.id, p.partnumber, p.name, p.description, p.price, p.cost, p.list, p.core, p.weight, p.brand, p.vendor_id, p.hotitem, p.special, p.picture, p.availability, p.db_busdepot, p.db_Imc, p.footnote, p.xref, p.unit_Measure, p.note, p.moreinfo from link l, partnumber p where l.partnumber_id=p.id and l.category_id=?";
		return simpleJdbcTemplate.query(sql, new BusDepotPartMapper(), id);
	}

	public List<BusDepotPart> getPartsByGroupId(int id) {
		String sql = "select distinct p.id, p.partnumber, p.name, p.description, p.price, p.cost, p.list, p.core, p.weight, p.brand, p.vendor_id, p.hotitem, p.special, p.picture, p.availability, p.db_busdepot, p.db_Imc, p.footnote, p.xref, p.unit_Measure, p.note, p.moreinfo from link l, partnumber p where l.partnumber_id=p.id and l.group_id=?";
		return simpleJdbcTemplate.query(sql, new BusDepotPartMapper(), id);
	}

	public List<BusDepotPart> getPartsByProductGroupId(int id) {
		String sql = "select distinct p.id, p.partnumber, p.name, p.description, p.price, p.cost, p.list, p.core, p.weight, p.brand, p.vendor_id, p.hotitem, p.special, p.picture, p.availability, p.db_busdepot, p.db_Imc, p.footnote, p.xref, p.unit_Measure, p.note, p.moreinfo from link l, partnumber p where l.partnumber_id=p.id and l.prodgroup_id=?";
		return simpleJdbcTemplate.query(sql, new BusDepotPartMapper(), id);
	}

	public List<ProductGroup> getProductGroup(int groupId, int categoryId, int modelId) {
		String sql = "select distinct pg.id, pg.description from link l, prodgroups pg where pg.id=l.prodgroup_id and l.group_id=? and l.category_id=? and l.model_id=?";
		return simpleJdbcTemplate.query(sql, new ProductGroupMapper(), groupId, categoryId, modelId);
	}
	
	

	private static final class ModelMapper implements RowMapper {
		public Model mapRow(ResultSet rs, int rowNum) throws SQLException {
			int id = rs.getInt("id");
			String name = rs.getString("description");
			int startYear = rs.getInt("from_year");
			int endYear = rs.getInt("to_year");
			Model model = new Model(id, name, startYear, endYear);
			return model;
		}
	};

	private static final class CategoryMapper implements RowMapper {
		public Category mapRow(ResultSet rs, int rowNum) throws SQLException {
			int id = rs.getInt("id");
			String name = rs.getString("description");
			Category category = new Category(id, name);
			return category;
		}
	};
	
	private static final class GroupMapper implements RowMapper {
		public Group mapRow(ResultSet rs, int rowNum) throws SQLException {
			int id = rs.getInt("id");
			String name = rs.getString("description");
			Group group = new Group(id, name);
			return group;
		}
	};
	
	private static final class ProductGroupMapper implements RowMapper {
		public ProductGroup mapRow(ResultSet rs, int rowNum) throws SQLException {
			int id = rs.getInt("id");
			String name = rs.getString("description");
			ProductGroup productGroup = new ProductGroup(id, name);
			return productGroup;
		}
	};

	private static final class BusDepotPartMapper implements RowMapper {
		public BusDepotPart mapRow(ResultSet rs, int rowNum)
				throws SQLException {
			int id = rs.getInt("id");
			String partNumber = rs.getString("partnumber");
			String name = rs.getString("name");
			String description = rs.getString("description");
			double price = rs.getDouble("price");
			double cost = rs.getDouble("cost");
			double listPrice = rs.getDouble("list");
			double core = rs.getDouble("core");
			double weight = rs.getDouble("weight");
			String brand = rs.getString("brand");
			int vendorId = rs.getInt("vendor_id");
			boolean hotItem = rs.getBoolean("hotitem");
			boolean special = rs.getBoolean("special");
			String pictureType = rs.getString("picture");
			String availability = rs.getString("availability");
			String DbBusDepot = rs.getString("db_busdepot");
			String DbImc = rs.getString("db_Imc");
			String footnote = rs.getString("footnote");
			String xref = rs.getString("xref");
			String unitMeasure = rs.getString("unit_Measure");
			String note = rs.getString("note");
			String moreInfo = rs.getString("moreinfo");
			
			BusDepotPart part = new BusDepotPart();
			part.setId(id);
			part.setPartNumber(partNumber);
			part.setName(name);
			part.setDescription(description);
			part.setPrice(price);
			part.setCost(cost);
			part.setListPrice(listPrice);
			part.setCore(core);
			part.setWeight(weight);
			part.setBrand(brand);
			part.setVendorId(vendorId);
			part.setHotItem(hotItem);
			part.setSpecial(special);
			part.setPictureType(pictureType);
			part.setAvailability(availability);
			part.setDbBusDepot(DbBusDepot);
			part.setDbImc(DbImc);
			part.setFootnote(footnote);
			part.setXref(xref);
			part.setUnitMeasure(unitMeasure);
			part.setNote(note);
			part.setMoreInfo(moreInfo); 

			return part;
		}
	};

}
